import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.Map;
import java.util.TreeMap;
import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;
import ocsf.server.ConnectionToClient;
import java.io.File;
import java.io.FileInputStream;

/**
 * This class contain the queries that are sent to the database.
 * Each action performed in the GUI have match query.
 * @author G_14
 * @version December 2011
 */


public class DBMethods  implements Serializable
{
//Class variables *************************************************
/**
* The statement will receive the database answer.
*/
	private Statement stmt;
	private OptWin addNewClassOpt = null;
	private OptWin addStudentToClassOpt = null;
	private OptWin addExOpt = null;
	private String sem;
	
//Constructors ****************************************************

/**
* Constructs an instance of the DBMethods.
*/
	public  DBMethods()	{}

/**
* This method check with the database whether the user is already
* logged or not even in the system.
* Otherwise the user is approved and the database raise the loged flag. 
* @param the connection with the database that was create in the EchoServer.
* @param message The string to be sent to the database.
* @return instance of login 
*/	
public Object checkValidation(Connection conn, Object msg, String t) {
	
		
		System.out.println("this is : " + msg.toString());
		Login log = (Login)msg;
		try 
		{				
			stmt = conn.createStatement();
			
			ResultSet res = stmt.executeQuery("SELECT * FROM " + t + " WHERE ID='"+log.getUserlogin()+"'");			
			if (res.next())
			{
				if (!(res.getString(2).equals(log.getPasswordlogin()))) 
				{
					log.setState(1);
					ErrWin err = new ErrWin ("Wrong Password");
					System.out.println("Error - wrong password");
				}
				else 
				{
					if (res.getInt(3) == 1) 
					{
						ErrWin err = new ErrWin ("sorry, you are already logged in");
						System.out.println("Error - already logged in");
						 log.setState(3);
					}
					if (res.getInt(3) == 9) 
					{
						ErrWin err = new ErrWin ("sorry, you are block");
						System.out.println("Error - you are block");
						 log.setState(3);
					}
					if (res.getInt(3) == 0) 
					{
						stmt.executeUpdate("UPDATE " + t + " SET loged=1 WHERE ID='"+log.getUserlogin()+"'");
						log.setState(4);
						return "logSucces";
					}
				}
			}
		      else 	
		      {
		    	  ErrWin err = new ErrWin ("sorry, wrong user id");
					System.out.println("Error - user does not exist");
					 log.setState(2);
					 return "logNotSucces";
		      }
			}
		catch (SQLException e) {	e.printStackTrace();}
		return "logNotSucces";
	}

/**
 * this method delete users from the data base
 * @param conn is connection 
 * @param msg is type User 
 * @param t string for the right table
 * @return user
 */
public Object delUser(Connection conn, Object msg, String t) {
		User user = (User)msg;
		try 
		{				
			stmt = conn.createStatement();
			ResultSet res=stmt.executeQuery("SELECT * FROM " + t + " WHERE ID='"+user.getUser()+"'");			
			if (res.next())  	
			{
				System.out.println("this user was removed");
				stmt.executeUpdate("DELETE FROM " + t + " WHERE ID='"+user.getUser()+"'");	
			}
			else 
			{
				  ErrWin err = new ErrWin ("this user does not exist");
				  System.out.println("Error - this user does not exist");
				  return "SQLErr";
			}
			
		}
		catch (SQLException e) {e.printStackTrace();}
		return user;

		}
/**
 * this method add users from the data base
 * @param conn is connection
 * @param msg is type user
 * @param t string for the right table
 * @return user
 */
public Object addUser(Connection conn, Object msg, String t) {
	User user = (User)msg;
	try 
	{				
		stmt = conn.createStatement();
		ResultSet res=stmt.executeQuery("SELECT * FROM " + t + " WHERE ID='"+user.getUser()+"'");			
		if (!res.next()) 	
		{
			System.out.println("this user was added");
			if (t.equals("teachers"))
			{
				System.out.println("INSERT INTO " + t + " VALUES ('"  + user.getUser() + " ',' " + user.getPassword() + "' , '0 ','"+ user.getName()+"','"+user.getHours()+"') ");	
				stmt.executeUpdate("INSERT INTO " + t + " VALUES ('"  + user.getUser() + " ',' " + user.getPassword() + "' , '0 ','"+ user.getName()+"','"+user.getHours()+"','0')");	
			}
			else
			{
				stmt.executeUpdate("INSERT INTO " + t + " VALUES ('"  + user.getUser() + " ',' " + user.getPassword() + "' , '0 ','"+ user.getName()+"') ");
			}
		}	
		else 	
		{
			  ErrWin err = new ErrWin ("this user already exist");
			  System.out.println("Error - this user already exist");
			  return "SQLErr";
		}
		
	}
	catch (SQLException e) {e.printStackTrace();}
	return user;

	}
/**
  * this method block user in the data base
 * @param conn is connection
 * @param msg is type string
 * @return null
 */
public Object BlockUser(Connection conn, Object msg) {
		String s = (String) msg;
		try 
		{				
			stmt = conn.createStatement();
			ResultSet res=stmt.executeQuery("SELECT * FROM  parents  WHERE ID='"+ s +"'");
			if (res.next())
			{
				System.out.println("this user was blocked");
				stmt.executeUpdate("UPDATE parents SET loged=9 WHERE ID='"+ s +"'");
			}
			else
			{
				  ErrWin err = new ErrWin ("this user is not exist");
					System.out.println("Error - this user is not exist");
			}
		}
			catch (SQLException e) {	e.printStackTrace();}
		return "SQLErr";
	}
/**
 * this method unblock user in the data base
 * @param conn is connection
 * @param msg is type string
 * @return null
 */
public Object UnBlockUser(Connection conn, Object msg) {
		String s = (String) msg;
		try 
		{				
			stmt = conn.createStatement();
			ResultSet res=stmt.executeQuery("SELECT * FROM  parents  WHERE ID='"+ s +"'");
			if (res.next())
			{
				System.out.println("this user was Unblocked");
				stmt.executeUpdate("UPDATE parents SET loged=0 WHERE ID='"+s+"'");
			}
			else
			{
				  ErrWin err = new ErrWin ("this user is not exist");
				  System.out.println("Error - this user is not exist");
			}
		}
			catch (SQLException e) {	e.printStackTrace();}
		return "SQLErr";
	}
/**
 * this method add parent to the data base
 * @param conn is connection
 * @param msg is type AddParent
 * @return the addParent or "err"
 */
public Object addParent(Connection conn, Object msg) {
		AddParent addParent = (AddParent)msg;
		try 
		{				
			stmt = conn.createStatement();
			ResultSet res1=stmt.executeQuery("SELECT * FROM students WHERE ID='"+addParent.getcID()+"'");			
			if (res1.next())  	
			{
				ResultSet res2=stmt.executeQuery("SELECT * FROM parents WHERE ID='"+addParent.getpID()+"'");	
				if (res2.next()) 	
				{
					System.out.println("this child was added");
					stmt.executeUpdate("INSERT INTO studentparent VALUES ('"  + addParent.getpID() + "' ,'"  + addParent.getcID() + "'  ) ");	
				}
				else
				{
					System.out.println("this parent was added");
					System.out.println("INSERT INTO parents VALUES ('"  + addParent.getpID() + " ', '" + addParent.getPass() +"' , ' 0' ,'"+addParent.getName()+"') ");	
					stmt.executeUpdate("INSERT INTO parents VALUES ('"  + addParent.getpID() + " ', '" + addParent.getPass() +"' , ' 0' ,'"+addParent.getName()+"') ");	
					stmt.executeUpdate("INSERT INTO studentparent VALUES ('"  + addParent.getpID() + " ','"  + addParent.getcID() + "'  ) ");	
	
				}
			}
			else //  student does not exist 	
			{
				 ErrWin err = new ErrWin ("This student does not exist, first creant this new student");
				 System.out.println("Error - this student does not exist, first creant this student");
				 return "ERR";
			}
			
		}
		catch (SQLException e) {e.printStackTrace();}
		return addParent;
		}
/**
 * this method add course to the data base
 * @param conn is connection
 * @param msg is type Course
 * @return null case of ERR or course Case success
 */
public Object addCourse(Connection conn, Object msg) {
		System.out.println("try adding course");
		Course course = (Course)msg;
		try 
		{		
			
			stmt = conn.createStatement();
			ResultSet resCourse=stmt.executeQuery("SELECT * FROM course WHERE ID='" + course.getCourseID() + "'");			
			if (!resCourse.next()) // course does not exist 	
			{
				ResultSet resUnit=stmt.executeQuery("SELECT * FROM teachingunit WHERE unitID='" + course.getCourseUnit() + "'");
				if (resUnit.next()) // teachingUnit exist 	
				{
					System.out.println("this course was added");
					stmt.executeUpdate("INSERT INTO  course VALUES ('"  + course.getCourseID() + "' , '" + course.getCourseName() +  "' , '" + course.getCourseUnit() + "' ,' " + course.getNumOfHours() + "'  ) ");
				}
				else // teachingUnit does not exist 	
				{
					  ErrWin err = new ErrWin ("this TeachingUnit is not exist");
					  System.out.println("Error - this TeachingUnit is not exist");
					  return "SQLErr";
				}
			}
			else //  course already exist 	
			{
				  ErrWin err = new ErrWin ("this CourseID already exist");
				  System.out.println("Error - this CourseID already exist");
				  return "SQLErr";
			}
			
		}
		catch (SQLException e) {e.printStackTrace();}
		return course;
	
		}
/**
 * this method add class the data base
 * @param conn is connection
 * @param msg is type cls
 * @return cls case of err or "classCreate" case of success
 */
public Object addClass(Connection conn, Object msg) {
		System.out.println("try adding class");
		Cls cls = (Cls)msg;
		try 
		{		
			
			stmt = conn.createStatement();
			ResultSet resCourse=stmt.executeQuery("SELECT * FROM class WHERE ID='" + cls.getClassID() + "'");			
			if (!resCourse.next()) // class does not exist 	
			{
	
					
					addNewClassOpt = new OptWin("Are you sure you want to add this new class?",1);
					while (addNewClassOpt.flg == -1);
					if (addNewClassOpt.flg == 1)
					{
						System.out.println("class was added");
						stmt.executeUpdate("INSERT INTO  class VALUES ('"  + cls.getClassID() + "' , '" + cls.getClassName() +   "'  ) ");
						return "classCreate";
					}
			}
			else // class exist 	
				{
				addNewClassOpt = new OptWin("This class already exist, Do you want to add students?",2);
				while (addNewClassOpt.flg == -1);
				if (addNewClassOpt.flg == 1)
					return "classCreate";
				else
					  System.out.println("Pay attention - this class already exist");
					  return cls;
				}
		}
		catch (SQLException e) {e.printStackTrace();}
		return cls;
	
	}
/**
 * this method add students to the table student list in the data base
 * @param conn is connection
 * @param msg is type studentList
 * @return "err" case of err or studentList case of success
 */
public Object addStudentToList(Connection conn, Object msg) {
		System.out.println("try adding student to list");
		StudentList sList = (StudentList)msg;
		try 
		{		
			
			stmt = conn.createStatement();
			System.out.println("this student was added");
			ResultSet res1=stmt.executeQuery("SELECT ID FROM students WHERE ID = '" +sList.getsID()+"'");			
			if (!res1.next()) // class does not exist 	
			{
					ErrWin err = new ErrWin ("This student ID is not exist");
					System.out.println("Error - this student ID is not exist");
					return "err";
			}
			else
			{
				if (sList.getType()==3)
				{
					stmt = conn.createStatement();
					ResultSet res2=stmt.executeQuery("SELECT ID FROM studentlist WHERE ID = '" +sList.getsID()+"' and type='3'");			
					if (!res2.next()) // class does not exist 	
					{
						stmt.executeUpdate("INSERT INTO  studentlist VALUES ('"  + sList.getType() + "' , '" + sList.getlID() +   "'  , '" + sList.getsID() +   "'  ,'') ");
					}
					else
					{
						addStudentToClassOpt = new OptWin("This student already exist in anther class, Do you want to change his class?",2);
							while (addStudentToClassOpt.flg == -1);
							if (addStudentToClassOpt.flg == 1)
							{
								System.out.println("UPDATE studentlist SET k='"+sList.getlID() +"' WHERE ID = '" +sList.getsID()+"' and type='3'");
								stmt.executeUpdate("UPDATE studentlist SET k='"+sList.getlID() +"' WHERE ID = '" +sList.getsID()+"' and type='3'");
							}	
							else
							{
								System.out.println("inseret new student");
								  return "SQLErr";
							}
					}
				}
				else
				{
					stmt.executeUpdate("INSERT INTO  studentlist VALUES ('"  + sList.getType() + "' , '" + sList.getlID() +   "'  , '" + sList.getsID() +   "'  ,'') ");
					return sList;
				}
			}	
		}
		catch (SQLException e) {e.printStackTrace();}
		return sList;
	}
/**
 * this method add open new semester the data base
 * @param conn is connection
 * @param msg is type semester
 * @return "err" case of err or "semCreate" case of success
 */
public Object openNewSem(Connection conn, Object msg) {
		System.out.println("try open new semester");
		Sem semester = (Sem)msg;
		try 
		{				
			stmt = conn.createStatement();
			ResultSet res=stmt.executeQuery("SELECT * FROM semester WHERE semID='" + semester.getSemID() + "'");			
			if (!res.next()) // user does not exist 	
			{
				sem = semester.getSemID();
				stmt.executeUpdate("UPDATE semester SET current=0");
				System.out.println("this sem was added: " +sem );
				stmt.executeUpdate("INSERT INTO semester VALUES ('"  + semester.getSemID() + " ',' " + semester.getSemName() +   " ',' " + semester.getStart()  + " ',' " + semester.getFinish() + "' , '1 ') ");	
				return "semCreate";
			}
			else //  user already exist 	
			{
				  ErrWin err = new ErrWin ("this semester ID already exist");
				  System.out.println("Error - this semseter ID already exist");
				  return "err";
			}
			
		}
		catch (SQLException e) {e.printStackTrace();}
		return "SQLErr";
	}

/**
 * this method  return the courses which the teacher study 
 * @param conn is connection
 * @param msg is type teacherID
 * @return "NoCourses" case of the teacher do not have courses or list that contain the teacher's courses 
 */
public Object getTeacherCourses(Connection conn, Object msg) {
	System.out.println("get teacher courses");
	ArrayList<String[]> l = new ArrayList<String[]>();
	String[] info = new String[2];
	String tID = (String)msg;
	try 
	{				
		stmt = conn.createStatement();
		System.out.println("SELECT * FROM classincourse WHERE semID = '"+ sem +"' and teacherID='" + tID + "' GROUP BY courseID");			

		ResultSet res=stmt.executeQuery("SELECT * FROM classincourse WHERE semID = '"+sem+"' and teacherID='" + tID + "' GROUP BY courseID");			
		if (!res.next()) 
		{
			return "NoCourses";
		}
		else 
		{		
			info[0] = res.getString(1);
			info[1] = res.getString(2);
			l.add(info); 
			while (res.next()) { 
				info = new String[2];
				info[0] = res.getString(1);
				info[1] = res.getString(2);
				l.add(info);  
			}
			return l;
		}
		
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQL Err";
}

/**
 * this method add return the course name
 * @param conn is connection
 * @param msg is type courseID
 * @return courseName
 */
public Object getCourseName(Connection conn, Object msg) {
	System.out.println("get courses name");
	String cID = (String)msg;
	try 
	{				
		stmt = conn.createStatement();
		ResultSet res=stmt.executeQuery("SELECT name FROM course WHERE ID='" + cID + "'");			
		if (!res.next()) 
		{
			return "NoCoursesInTheSystem";
		}
		else 	
		{
			  return res.getString(1);
		}
		
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQL Err";
}
/**
 * this method return the student's assignment
 * @param conn is connection
 * @param msg is type studentID
 * @return list of the student assignment
 */
public Object receiveAssn(Connection conn, Object msg) {
	 System.out.println("Try to open student assignments");
	 Assignment ass = new Assignment();
	 ArrayList<Assignment> l = new ArrayList<Assignment>();
	 String id = (String)msg;
	 try 
	 {    
		  stmt = conn.createStatement();
		  System.out.println("SELECT * FROM studentass WHERE semID = '"+sem+"' and studentID='"+id+"'");
		  ResultSet res=stmt.executeQuery("SELECT * FROM studentass WHERE semID = '"+sem+"' and studentID='"+id+"' and status='0'");
		  if (!res.next()) 
		  {
			  return "NoAssignment";
		  }
		  else
		  {
			  ass.setCourseID(res.getString(2));
			  ass.setAssNumber(res.getInt(3));
			  l.add(ass);
			  while (res.next()) 
			  {
				  ass = new Assignment();
				  ass.setCourseID(res.getString(2));
				  ass.setAssNumber(res.getInt(3));
				  l.add(ass);
			  }
			  return l;	  
		  }
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "NoAssignment";
	}

/**
 * this method add new assignment to the data base
 * @param conn is connection
 * @param msg is type Assignment
 * @return "SQLErr" case of err or true case of success
 */
public Object addAss(Connection conn, Object msg) {
	System.out.println("add new assignment");
	Assignment ass = (Assignment)msg;
	MyFile myFile = new MyFile(ass.getName());
	Integer assNum;
	ArrayList<String> l1 = new ArrayList<String>();
	ArrayList<String> l2 = new ArrayList<String>();
	String s2;
	String s1;
	try 
	{				
		stmt = conn.createStatement();
		System.out.println("this ass was added");
		ResultSet resAss = stmt.executeQuery("SELECT max(assNum)  FROM assignment WHERE semID = '"+sem+"' and courseID='"+ass.getCourseID()+"' and teacherID='"+ass.getTeacherID()+"'");
		if (resAss.next())
		{
			assNum = resAss.getInt(1);
			assNum++;
		}
		else {assNum = 1;}
	    myFile.initArray(ass.getSize());
	    myFile.setSize(ass.getSize());
	    myFile.setMybytearray(ass.getF());
	    
	    String n = myFile.getName();
		String fileName = ass.getCourseID()+"_"+assNum.toString()+"_"+ass.getTeacherID()+"_"+n;
		myFile.setName(fileName);
	    
	    Save save = new Save(myFile);
		save.saveFile(myFile);
		File f = save.getFf();
		System.out.println("INSERT INTO assignment VALUES ('"  + ass.getCourseID() + "','" + assNum +   "','" + myFile.getName()  + "','" + ass.getSubDate() + "' , '"+myFile.getMybytearray()+"','" +ass.getTeacherID()+"','"+ass.getSize()+"','"+sem+"' ) ");	
		stmt.executeUpdate("INSERT INTO assignment VALUES ('"  + ass.getCourseID() + "','" + assNum +   "','" + myFile.getName()  + "','" + ass.getSubDate() + "' , '"+myFile.getMybytearray()+"','" +ass.getTeacherID()+"','"+ass.getSize()+"','"+sem+"' ) ");	
		ResultSet res1 = stmt.executeQuery("SELECT classID FROM classincourse WHERE semID = '"+sem+"' and courseID='"+ass.getCourseID()+"' and teacherID='"+ass.getTeacherID()+"'");
		System.out.println("SELECT classID FROM classincourse WHERE semID = '"+sem+"' and courseID='"+ass.getCourseID()+"' and teacherID='"+ass.getTeacherID()+"'");
		if (res1.next())
		{
			l1.add(res1.getString(1));
			while (res1.next()) 
			{
				l1.add(res1.getString(1));
			}
			for (int i=0 ;i<l1.size();i++)
			{
				s1 = l1.get(i);
				System.out.println("SELECT ID FROM studentlist WHERE semID = '"+sem+"' and type='3' and k='"+ s1 +"'");
				ResultSet res2 = stmt.executeQuery("SELECT ID FROM studentlist WHERE semID = '"+sem+"' and type='3' and k='"+ s1 +"'");
				if (res2.next())
				{
					l2.add(res2.getString(1));
					while (res2.next()) 
					{
						l2.add(res2.getString(1));
					}
					for (int j=0 ;j<l2.size();j++)
					{
						s2 = l2.get(j);
						System.out.println("INSERT INTO studentass VALUES ('" + s2 + "','"  + ass.getCourseID() + " ',' " + assNum +   " ') ");			
						stmt.executeUpdate("INSERT INTO studentass VALUES ('" + s2 + "','"  + ass.getCourseID() + " ',' " + assNum +   " ','"+sem+"','0') ");	
					}
				}
			}
		}
		return true;
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQLErr";
}

/**
 * this method return the student's assignment
 * @param conn is connection
 * @param msg is type string array that contain studentID, courseID and assNumber
 * @return "SQLErr" case of err or MyFile case of success
 */
public Object openAssn(Connection conn, Object msg) {
	MyFile myFile = null;
	Assignment ass = new Assignment();
	System.out.println("Try to open assignmentXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx");
	String arr[] = (String[])msg;
	try 
	{    
		System.out.println("Try to open assignment");
		  stmt = conn.createStatement();
		  System.out.println("SELECT * FROM studentass WHERE semID='"+sem+"' and studentID='"+arr[0]+"' and assNum='"+arr[2]+"' and courseID='"+ arr[1]+"'");
		  ResultSet res=stmt.executeQuery("SELECT * FROM studentass WHERE semID='"+sem+"' and studentID='"+arr[0]+"' and assNum='"+arr[2]+"' and courseID='"+ arr[1]+"'");
		  if(res.next())
		  {
			  ass.setCourseID(res.getString(2));
			  ass.setAssNumber(res.getInt(3));
			  System.out.println("SELECT k FROM studentlist WHERE semID='"+sem+"' and ID='"+arr[0]+"' and type='3'");
			  ResultSet res1=stmt.executeQuery("SELECT k FROM studentlist WHERE semID='" +sem+ "' and ID='"+arr[0]+"' and type='3'");
			  if(res1.next())
			  {
				  String classID = res1.getString(1);
				  System.out.println("class:     "+classID);
				  System.out.println("SELECT teacherID FROM classincourse WHERE semID='"+sem+"' and classID='"+classID+"'and courseID='"+arr[1]+"'");
				  ResultSet res2=stmt.executeQuery("SELECT teacherID FROM classincourse WHERE semID='"+sem+"' and classID='"+classID+"'and courseID='"+arr[1]+"'");
				  if(res2.next())
				  {
					  ass.setTeacherID(res2.getString(1));
					  System.out.println("teacher:       "+ass.getTeacherID());
				  
					  System.out.println("SELECT * FROM assignment WHERE semID='"+sem+"' and teacherID='"+ass.getTeacherID()+"'and courseID='"+arr[1]+"'and assNum='" +arr[2]+"'");
					  ResultSet res3=stmt.executeQuery("SELECT * FROM assignment WHERE semID='"+sem+"' and teacherID='"+ass.getTeacherID()+"'and courseID='"+arr[1]+"'and assNum='" +arr[2]+"'");
					  if(res3.next())
					  {
						  
						  File f = new File ("C://group14//"+res3.getString(3));
						  Browse b = new Browse(f);
						  myFile = b.getFile();
					      System.out.println("xxxxxxxxxxxxx: "+myFile.toString());
					      if (myFile!=null)
					    	  return myFile;
					  }
				  }
			  }
		  }
	
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";
}

	 
/**
 * this method set the status of the user to"logOut" on the data base
 * @param conn is connection
 * @param msg is type Login
 * @return "SQLErr" case of err or "logOut" case of success
 */
public Object logOut(Connection conn, Object msg) {

	System.out.println("this is : " + msg.toString());
	Login log = (Login)msg;
	try 
	{		
		String t = null;
		switch (log.getUserType())
		{
			case 1:
				t = "students";
				break;	
			case 2:
				t = "parents";
				break;	
			case 3:
				t = "teachers";
				break;	
		}
		stmt = conn.createStatement();
		System.out.println("UPDATE " + t + " SET loged=0 WHERE ID='"+log.getUserlogin()+"'");
		stmt.executeUpdate("UPDATE " + t + " SET loged=0 WHERE ID='"+log.getUserlogin()+"'");
		return "logOut";
	}
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";	
}


/**
 * this method return the current semester
 * @param conn is connection
 * @return "SQLErr" case of err or semesterID case of success
 */
public Object currSem(Connection conn) {

	System.out.println("current Semester " );
	try 
	{		
		String s = null;
		stmt = conn.createStatement();
		 ResultSet res=stmt.executeQuery("SELECT semID FROM semester WHERE current='1'");
		  if(res.next())
		  {
			  s = res.getString(1);
			  sem = s;
			  return s;
		  }
	}
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";	
}

/**
 * this method return all the courses that in the data base
 * @param conn is connection
 * @return "SQLErr" case of err, "NoCourses"case there arn't courses in the data base  or list that contain the courseID case of success
 */
public Object returnCourses(Connection conn) {
	ArrayList<String> l = new ArrayList<String>();
	System.out.println("return courses list" );
	try 
	{		
		stmt = conn.createStatement();
		System.out.println("SELECT ID FROM course");
		 ResultSet res=stmt.executeQuery("SELECT ID FROM course ");
		  if (!res.next()) // user has assignment 
		  {
			  return "NoCourses";
		  }
		  else
		  {
			  l.add(res.getString(1));
			  while (res.next()) 
			  {
				  l.add(res.getString(1));
			  }
			  return l;	  
		  }
	}
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";	
}

/**
 * this method return all the courses that in the data base
 * @param conn is connection
 * @return "SQLErr" case of err, "NoClasss"case there arn't courses in the data base  or list that contain the classID case of success
 */
public Object returnClass(Connection conn) {
	ArrayList<String> l = new ArrayList<String>();
	System.out.println("return class list" );
	try 
	{		
		stmt = conn.createStatement();
		System.out.println("SELECT ID FROM class");
		 ResultSet res=stmt.executeQuery("SELECT ID FROM class ");
		  if (!res.next()) // user has assignment 
		  {
			  return "NoClasses";
		  }
		  else
		  {
			  l.add(res.getString(1));
			  while (res.next()) 
			  {
				  l.add(res.getString(1));
			  }
			  return l;	  
		  }
	}
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";	
}

/**
 * this method return all the courses that in the data base
 * @param conn is connection
 * @return "SQLErr" case of err, "NoCourses"case there arn't courses in the data base  or list that contain the courseID case of success
 */
public Object returnTechers(Connection conn, Object msg) {
	String cID = (String)msg;
	System.out.println("baaaaaaaaaaaa");
	ArrayList<String> tIDl = new ArrayList<String>();
	ArrayList<String> tIDl2 = new ArrayList<String>();
	String uID = null;
	String tID = null;
	int h = 0;
	try 
	{				
		stmt = conn.createStatement();
		System.out.println("SELECT * FROM course WHERE ID='" + cID + "'");	
		ResultSet res1=stmt.executeQuery("SELECT * FROM course WHERE ID='" + cID + "'");			
		if (!res1.next()) // no course	
		{
			return "NoClassInTheSystem";
		}
		else //  return unit ID	
		{
			   uID = res1.getString(3);
			   h = res1.getInt(4);
			   System.out.println("SELECT * FROM unitperteacher WHERE unitId='"+uID+"'" );	
			   ResultSet res2 = stmt.executeQuery("SELECT * FROM unitperteacher WHERE unitId='"+uID+"'" );	
				if (!res2.next()) // no course	
				{
					return "NoClassInTheSystem";
				}
				else
				{
					tIDl.add(res2.getString(2));
					while (res2.next()) 
					{
						tIDl.add(res2.getString(2));
					}
					for (int j=0 ;j<tIDl.size();j++)
					{
						tID = tIDl.get(j);
						System.out.println("SELECT * FROM teachers WHERE ID='"+tID+"' and (weeklyHourse-totalHourse)>"+h );	
						ResultSet res3 = stmt.executeQuery("SELECT * FROM teachers WHERE ID='"+tID+"' and (weeklyHourse-totalHourse)>"+h );	
						if (!res3.next()) // user has assignment 
						  {
								
						  }
						  else
						  {
							  	tIDl2.add(res3.getString(1));
							  	while (res3.next()) 
							  	{
							  		tIDl2.add(res3.getString(1));
							  	}  
						  }
					}
					if (!tIDl2.isEmpty())
						return tIDl2;	
				}
		}
		
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQL Err";
}

/**
 * this method return the class name
 * @param conn is connection
 * @param msg is type classID
 * @return "SQLErr" case of err or "NoClassInTheSystem" case the class not exist or the class name
 */
public Object getClassName(Connection conn, Object msg) {
	System.out.println("get class name");
	String cID = (String)msg;
	try 
	{				
		stmt = conn.createStatement();
		System.out.println("SELECT name FROM class WHERE ID='" + cID + "'");	
		ResultSet res=stmt.executeQuery("SELECT name FROM class WHERE ID='" + cID + "'");			
		if (!res.next()) // no class	
		{
			return "NoClassInTheSystem";
		}
		else //  return class name	
		{
			  return res.getString(1);
		}
		
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQL Err";
}

/**
 * this method return the teacherExceptions 
 * @param conn is connection
 * @return "SQLErr" case of err or "NoExceptions" case there are no exceptions or list that contain the TException  
 */
public Object TeacherExceptions(Connection conn) {
	 System.out.println("Try to get Exceptions");
	 TException Ex;
	 LinkedList<TException> list = new LinkedList<TException>();
	 try 
	 {    
		  stmt = conn.createStatement();
		  ResultSet res=stmt.executeQuery("SELECT * FROM Texception WHERE status='2'");
		  if (!res.next()) // user has assignment 
		  {
			  return "NoExceptions";
		  }
		  else
		  {
			  do  
			  {
			  						/*exceptionID*/    /* status  */	/* classID */	  /* courseID */	/* TeacherID */	/*  comment */ /* semID */
				  Ex = new TException(res.getString(1), res.getInt(2), res.getString(3),res.getString(4) , res.getString(5) ,res.getString(6),res.getString(7));
				  list.add(Ex);
			  }while(res.next());
			  return list;	  
		  }
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";
}

/**
 * this method return the student's Exceptions 
 * @param conn is connection
 * @return "SQLErr" case of err or "NoExceptions" case there are no exceptions or list that contain the SException  
 */
public Object ADDExceptions(Connection conn) {
	 System.out.println("Try to get Exceptions");
	 SException Ex;
	 LinkedList<SException> list = new LinkedList<SException>();
	 try 
	 {    
		  stmt = conn.createStatement();
		  ResultSet res=stmt.executeQuery("SELECT * FROM Sexception WHERE Add_Rmv = '1' and status='2'");
		  if (!res.next()) // user has assignment 
		  {
			  return "NoExceptions";
		  }
		  else
		  {
			  do  
			  {
				  						/*exceptionID*/  /* status  */	/* StudentID */	 /* Add_Rmv */	/*  comment */     /* semID */		/* CourseID*/
				  Ex = new SException(res.getString(1), res.getInt(2), res.getString(3),res.getInt(4) , res.getString(5) ,res.getString(6),res.getString(7));
				  list.add(Ex);
			  }while(res.next());
			  return list;	  
		  }
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";
}

/**
 * this method return the student's remove Exceptions 
 * @param conn is connection
 * @return "SQLErr" case of err or "NoExceptions" case there are no exceptions or list that contain the SException  
 */
public Object RMVExceptions(Connection conn) {
	 System.out.println("Try to get Exceptions");
	 SException Ex;
	 LinkedList<SException> list = new LinkedList<SException>();
	 try 
	 {    
		  stmt = conn.createStatement();
		  ResultSet res=stmt.executeQuery("SELECT * FROM Sexception WHERE Add_Rmv = '2' and status='2'");
		  if (!res.next()) // user has assignment 
		  {
			  return "NoExceptions";
		  }
		  else
		  {
			  do  
			  {
				  						/*exceptionID*/  /* status  */	/* StudentID */	 /* Add_Rmv */	/*  comment */     /* semID */		/* CourseID*/
				  Ex = new SException(res.getString(1), res.getInt(2), res.getString(3),res.getInt(4) , res.getString(5) ,res.getString(6),res.getString(7));
				  list.add(Ex);
			  }while(res.next());
			  return list;	  
		  }
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";
}

/**
 * this method return the teacher name
 * @param conn is connection
 * @param msg is type teacherID
 * @return "SQLErr" case of err or "NoTeacherInTheSystem" case the teacher not exist or the teacher name
 */
public Object getTeacherName(Connection conn, Object msg) {
	System.out.println("get teacher name");
	String tID = (String)msg;
	try 
	{				
		stmt = conn.createStatement();
		ResultSet res=stmt.executeQuery("SELECT name FROM teachers WHERE ID='" + tID + "'");			
		if (!res.next()) //  no courses	
		{
			return "NoTeachersInTheSystem";
		}
		else //  return course name	
		{
			  return res.getString(1);
		}
		
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQL Err";
}

/**
 * this method change teacher according to the TException
 * @param conn is connection
 * @param msg is type TException
 * @return  "everything is ok!" case of success
 * @throws SQLException
 */
public Object ChangeTeacher(Connection conn, Object object) throws SQLException {
	System.out.println("Try to change teacher");
	stmt = conn.createStatement();
	TException EX = (TException)object;
	stmt.executeUpdate("UPDATE classincourse SET teacherID='"+EX.getTeacherID() +"' WHERE classID='"+EX.getClassID()+ "'and courseID='"+EX.getCourseID()+"'");
	stmt.executeUpdate("UPDATE TException SET status='1' WHERE ID='"+EX.getExceptionID()+"'");
	return "everything is ok!";
}

/**
 * this method insert student to course list according to the SException
 * @param conn is connection
 * @param msg is type SException
 * @return  "everything is ok!" case of success
 * @throws SQLException
 */
public Object SpecialAdd(Connection conn, Object object) throws SQLException {
	System.out.println("Try to change teacher");
	stmt = conn.createStatement();
	SException EX = (SException)object;
	stmt.executeUpdate("INSERT INTO studentlist VALUES('3,','SpeicalClass,"+EX.getStudentID()+"',"+EX.getSemID()+"')");
	stmt.executeUpdate("UPDATE SException SET status='1' WHERE ID='"+EX.getExceptionID()+"'");
	return "everything is ok!";
}

/**
 * this method remove student from course list according to the SException
 * @param conn is connection
 * @param msg is type SException
 * @return  "everything is ok!" case of success
 * @throws SQLException
 */
public Object SpecialRMV(Connection conn, Object object) throws SQLException {
	System.out.println("Try to change teacher");
	stmt = conn.createStatement();
	SException EX = (SException)object;
	stmt.executeUpdate("DELETE FROM studentlist WHERE ID='"+EX.getStudentID()+"'and semID='"+EX.getSemID()+"'and type='3' AND k='"+EX.getCourseID()+"'");
	stmt.executeUpdate("UPDATE SException SET status='1' WHERE ID='"+EX.getExceptionID()+"'");
	return "everything is ok!";
}

/**
 * this method return the student's grades
 * @param conn is connection
 * @param msg is type String array
 * @return GQuery
 * @throws SQLException
 */
public Object GetGrades(Connection conn, Object object) throws SQLException {
	stmt = conn.createStatement();
	Object temp[]=(Object[]) object;
	System.out.println("I HAVE THOSE: "+temp[0].toString()+" "+temp[1].toString());
	GQuery GQ;
	ResultSet res = stmt.executeQuery("SELECT count(grade) AS Count, max(grade) AS Max, min(grade) as Min, avg(grade) as AVG  FROM grades WHERE studentID IN (SELECT ID FROM studentlist where type='"+temp[1].toString()+"' and k='"+temp[0].toString()+"')" );
	res.next();
					//	GQuery(String iD, int grade, int count, int max, int min, int avg)
		GQ =  new GQuery(temp[0].toString(),res.getInt(1),res.getInt(2),res.getInt(3),res.getInt(4));
	System.out.println("ClassID: "+GQ.getID()+" Count: "+GQ.getCount()+" AVG: "+GQ.getAvg()+" Min: "+GQ.getMin()+" Max: "+GQ.getMax());
	return GQ;
}

/**
 * this method return list of the ID
 * @param conn is connection
 * @param msg is type string of the table
 * @return list that contain to the ID's
 * @throws SQLException
 */
public Object GetIDs(Connection conn, Object object) throws SQLException {
	LinkedList<String> list = new LinkedList<String>();
	stmt = conn.createStatement();
	ResultSet res = stmt.executeQuery("SELECT ID from "+ object.toString() );
	while (res.next())
		list.add(res.getString(1));
	return list;
}

/**
 * this method return list of the teacher's class
 * @param conn is connection
 * @param msg is type string of the table
 * @return list that contain to the 
 * @throws SQLException
 */
public Object TeacherClasses(Connection conn, Object object) throws SQLException {
	
	Object temp[] = (Object[]) object;
	stmt = conn.createStatement();
	ResultSet res = stmt.executeQuery("SELECT "+temp[1].toString()+" from classincourse where "+temp[2].toString()+"='"+temp[0].toString()+"'group by "+temp[1].toString() );
	LinkedList<String> list = new LinkedList<String>();
	while (res.next()){
		list.add(res.getString(1)); System.out.println("TeacherCLasses: "+res.getString(1));}
	return list;
}

/**
 * this method add class and teacher to course
 * @param conn is connection
 * @param msg is type string of the arrayList
 * @return true case of success or "SQLErr" case of err
 */
public Object addClassToCourse(Connection conn, Object msg) {
	ArrayList<String> students = (ArrayList<String>)msg;
	int size = students.size();
	int h;
	try 
	{				
		stmt = conn.createStatement();
		ResultSet resH=stmt.executeQuery("SELECT numOfHours FROM course WHERE ID='" + students.get(0) + "'");
		if (resH.next())
		{
			h = resH.getInt(1);
		}
		else {h=0;}
		resH=stmt.executeQuery("SELECT totalHourse FROM teachers WHERE ID='" + students.get(size-1) + "'");
		if (resH.next())
		{
			h = h+resH.getInt(1);
		}
		else ;
		System.out.println("UPDATE teachers SET totalHourse='"+h+"' WHERE ID='"+students.get(size-1)+"'");	
		stmt.executeUpdate("UPDATE teachers SET totalHourse='"+h+"' WHERE ID='"+students.get(size-1)+"'");
		System.out.println("INSERT INTO classincourse VALUES ('"  + students.get(size-2) + " ',' "  + students.get(0) +  " ','"  + students.get(size-1) + "','" + sem + "') ");			
		stmt.executeUpdate("INSERT INTO classincourse VALUES ('"  + students.get(size-2) + "','"  + students.get(0) +  "','"  + students.get(size-1) + "','" + sem + "') ");
		System.out.println("INSERT INTO courseinsem VALUES ('"  + students.get(0) + "','" + sem + "') ");			
		stmt.executeUpdate("INSERT INTO courseinsem VALUES ('"  + students.get(0) + "','" + sem + "') ");
		//System.out.println("SELECT ID FROM studentlist WHERE type ='3' and k= '" + students.get(0) + "'");
		
			for (int i=1;i<students.size()-2;i++)
			{
				System.out.println("INSERT INTO studentlist VALUES ('2','"  + students.get(0) + " ',' " + students.get(i) +   " ','" + sem + "') ");			
				stmt.executeUpdate("INSERT INTO studentlist VALUES ('2','"  +students.get(0) + " ',' " + students.get(i) +   " ','" + sem + "') ");			
			}
		
		return true;
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQL Err";
}

/**
 * this method return list of the student that can take the course
 * @param conn is connection
 * @param msg is type string array
 * @return list that contain the students that can take the course
 * @throws SQLException
 */
public Object classPreCourse(Connection conn, Object msg) throws SQLException {
	String arr[] = (String[])msg;
	int flg = 1;
	ArrayList<String> studentList   = new ArrayList<String>();
	ArrayList<String> preCourseList = new ArrayList<String>();
	ArrayList<String> returnList    = new ArrayList<String>();
	returnList.add(arr[0]);		
		stmt = conn.createStatement();
		ResultSet res1=stmt.executeQuery("SELECT preCourseID FROM precourse WHERE courseID= '" +arr[0]+"'");
		if (!res1.next())
		{
			System.out.println("SELECT ID FROM studentlist WHERE type='3' and k='" +arr[1]+"'");	
			ResultSet res2 = stmt.executeQuery("SELECT ID FROM studentlist WHERE type='3' and k='" +arr[1]+"'");	
			if (!res2.next()) //  no students	
			{
				System.out.println("Empty ");
				return returnList;
			}
			else //  return students in class	
			{
				System.out.println("Add to student list ");
				do  
				{
					returnList.add(res2.getString(1));
					System.out.println(res2.getString(1));
				}while(res2.next());
			}
		}
		else
		{
			do  
			{preCourseList.add(res1.getString(1)); System.out.println("I just added "+res1.getString(1)+" as a precourse to the precourselist");
			}while(res1.next());
			System.out.println("SELECT ID FROM studentlist WHERE type='3' and semID = '"+sem+"' and k= '" +arr[1]+"'");	
			ResultSet res2 = stmt.executeQuery("SELECT ID FROM studentlist WHERE type='3' and k= '" +arr[1]+"'");	
			if (!res2.next()) //  no students	
			{
				return returnList;
			}
			else //  return students in class	
			{
				do  
				{studentList.add(res2.getString(1));
				}while(res2.next());
				for (int i=0;i<studentList.size();i++)
				{
					for (int j=0;j<preCourseList.size();j++)
					{
						System.out.println("begin for  - flg = "+flg);
						flg = 1;
						System.out.println("SELECT grade FROM grades WHERE studentID='"+studentList.get(i)+"' and courseID = '"+preCourseList.get(j)+"' ");	
						ResultSet res3 = stmt.executeQuery("SELECT grade FROM grades WHERE studentID='"+studentList.get(i)+"' and courseID = '"+preCourseList.get(j)+"' ");	
						if (!res3.next()) //  student didn't take this preCourse yet
						{
							flg = 0;
							System.out.println("not take  - flg = "+flg);	
							String op = "Student "+studentList.get(i)+ " didn't take preCourse "+ preCourseList.get(j)+", add Exception?";
							addExOpt = new OptWin(op,2);
							while (addExOpt.flg == -1);
							if (addExOpt.flg == 1)
							{
								String ex = "did not take" + preCourseList.get(j);
								addExRemoveStudent(conn, studentList.get(i), arr[0],ex);
								
							}
							else
							{
								  System.out.println("Pay attention - this class already exist");
							}
							
						}
						else
						{
							if (res3.getInt(1)<55)
							{
								flg = 0;
								System.out.println("not pass  - flg = "+flg);
								String op = "Student "+studentList.get(i)+ " didn't pass preCourse "+ preCourseList.get(j)+", add Exception?";
								addExOpt = new OptWin(op,2);
								while (addExOpt.flg == -1);
								if (addExOpt.flg == 1)
								{
									String ex = "fail" + preCourseList.get(j);
									addExRemoveStudent(conn, studentList.get(i),arr[0],ex);
									 
								}
								else
								{
									System.out.println("Pay attention - this class already exist");
								}
							
							}
						}	
						System.out.println("befor if - flg = "+flg);
						if (flg == 1)
						{
								System.out.println("add to list - " + studentList.get(i));	
								returnList.add(studentList.get(i));
						}
						
					}//for1
				} // for2	
				return returnList;	 
			}//else1
		}//else2
		return returnList;
}

/**
 * this method add remove student Ex
 * @param conn is connection
 * @param sID string of studentID
 * @param cID string of courseID
 * @param comment string of comment
 * @return list that contain to the 
 */
private boolean addExRemoveStudent(Connection conn, String sID, String cID, String comment) {
	int ExID;
	try 
	{				
		stmt = conn.createStatement();
		ResultSet resEx = stmt.executeQuery("SELECT max(ID)  FROM sexception");
		if (resEx.next())
		{
			ExID = resEx.getInt(1);
			ExID++;
		}
		else {ExID = 1;}
		System.out.println("INSERT INTO sexception VALUES ('"  + ExID + "','2',' "  + sID + "' ,'2','" + comment + "','"  + sem + "','" + cID + "') ");			
		stmt.executeUpdate("INSERT INTO sexception VALUES ('"  + ExID + "','2',' "  + sID + "' ,'2','" + comment + "','"  + sem + "','" + cID + "') ");			
		return true;
	}
	catch (SQLException e) {e.printStackTrace();}
		return false;
	
}

/**
 * this method return the student name
 * @param conn is connection
 * @param msg is string studentID
 * @return the student name or "SQLErr" case of err
 */
public Object getStudentName(Connection conn, Object msg)  {
	System.out.println("get student name");
	String sID = (String)msg;
		try {
			stmt = conn.createStatement();
		System.out.println("SELECT name FROM students WHERE ID='"+sID+"'");
		ResultSet res=stmt.executeQuery("SELECT name FROM students WHERE ID='"+sID+"'");
		if(res.next())
			  return res.getString(1);
			  
		} catch (SQLException e) {e.printStackTrace();}
		return "SQLErr";
	}

/**
 * this method return list of the student grades
 * @param conn is connection
 * @param msg is string studentID
 * @return list that contain the student grades or "SQLErr" case of err
 * @throws SQLException
 */
public Object StudentsGrades(Connection conn, Object msg) throws SQLException {
	String sID = (String)msg;
	LinkedList<Grades> list = new LinkedList<Grades>();
	//	Grades G;
	stmt = conn.createStatement();
	ResultSet res = stmt.executeQuery("SELECT * FROM grades WHERE studentID='" + sID + "'");
	while (res.next())
	 {
		Grades G = new Grades(res.getString(1),res.getString(2),res.getString(3),res.getInt(4),res.getString(5),res.getString(6),res.getString(7));
		list.add(G);
	 }
	if (list.size()==0) return "no grades for this student";
	return list;
 }

/**
 * this method return the student name
 * @param conn is connection
 * @param msg is string studentID
 * @return the student name or "SQLErr" case of err
 */
public Object StudentEnrolled(Connection conn, Object msg) throws SQLException {
	String sID = (String)msg;
	LinkedList<StudentList> list = new LinkedList<StudentList>();
	stmt = conn.createStatement();
	ResultSet res = stmt.executeQuery("SELECT * FROM studentlist WHERE ID='" + sID + "'");
	while (res.next())
	 {
		StudentList s = new StudentList(res.getInt(1),res.getString(2),res.getString(3),res.getString(4));
		list.add(s);
	 }
	if (list.size()==0) return "The student is not enrolled anywhere";
	return list;
}

/**
 * this method return the assignment number
 * @param conn is connection
 * @param msg is array of strings 
 * @return the number of the assignment
 */
public Object GetNumberAss(Connection conn, Object msg) {
	int num=-1;
	String arr[] = (String[])msg;
	try 
	{				
		stmt = conn.createStatement();
		System.out.println("SELECT * FROM assignment WHERE courseID='"+arr[1]+"' and teacherID='"+arr[0]+"' and semID='"+sem+"'");
		ResultSet res=stmt.executeQuery("SELECT * FROM assignment WHERE courseID='"+arr[1]+"' and teacherID='"+arr[0]+"' and semID='"+sem+"'");
		do 
		{
			num++;
		}
		while (res.next());
		return num;
	}
	catch (SQLException e) {e.printStackTrace();}
	return "SQL Err";
}

/**
 * this method return the teacher data
 * @param conn is connection
 * @param msg is string teacherID
 * @return the Teacher 
 * @throws SQLException
 */
public Teacher TeacherData(Connection conn, Object object) throws SQLException {
	String id = (String) object;
	stmt = conn.createStatement();
	ResultSet res=stmt.executeQuery("SELECT * FROM teachers WHERE ID='"+id+"'");
	res.next();
	Teacher T =new Teacher(id,res.getString(4),res.getInt(5),res.getInt(6));
	return T;
}

/**
 * this method return the teacher unit
 * @param conn is connection
 * @param msg is string teacherID
 * @return the linked list that contain the unit id of this teacher 
 * @throws SQLException
 */
public LinkedList<String> UnitForTeacher(Connection conn, Object object) throws SQLException {
	String id = (String)object;
	LinkedList<String> list = new LinkedList<String>();
	stmt = conn.createStatement();
	ResultSet res=stmt.executeQuery("SELECT unitid FROM unitperteacher WHERE teacherID='"+id+"'");
	while (res.next()) list.add(res.getString(1));
	return list;
}

/**
 * this method return the class in course
 * @param conn is connection
 * @return the class in course 
 * @throws SQLException
 */
public LinkedList<ClassInCourse> ClassInCourse(Connection conn) throws SQLException {
	stmt = conn.createStatement();
	LinkedList<ClassInCourse> list = new LinkedList<ClassInCourse>();
	ResultSet res=stmt.executeQuery("SELECT * FROM classincourse");
	while (res.next())
	{
		ClassInCourse C = new ClassInCourse(res.getString(1),res.getString(2),res.getString(3),res.getString(4));
		list.add(C);
	}
	return list;
}

/**
 * this method add the submit assignment to the data base
 * @param conn is connection
 * @param msg is string Sub
 * @return "sucss" or "SQLErr" case of err
 */
public Object SubmitAss(Connection conn, Object msg) {
	MyFile myFile = null;
	Assignment ass = new Assignment();
	System.out.println("SubmitAsssssssssssssssssssssssssssss");
	Sub submit = (Sub)msg;
	try 
	{    
		System.out.println("Try to open assignment");
		  stmt = conn.createStatement();
		  System.out.println("SELECT * FROM studentass WHERE semID='"+sem+"' and studentID='"+submit.getStudentID()+"' and assNum='"+submit.getAssNum()+"' and courseID='"+ submit.getCourseID()+"'");
		  ResultSet res=stmt.executeQuery("SELECT * FROM studentass WHERE semID='"+sem+"' and studentID='"+submit.getStudentID()+"' and assNum='"+submit.getAssNum()+"' and courseID='"+ submit.getCourseID()+"'");
		  if(res.next())
		  {
			  ass.setCourseID(res.getString(2));
			  ass.setAssNumber(res.getInt(3));
			  System.out.println("SELECT k FROM studentlist WHERE semID='"+sem+"' and ID='"+submit.getStudentID()+"' and type='3'");
			  ResultSet res1=stmt.executeQuery("SELECT k FROM studentlist WHERE semID='" +sem+ "' and ID='"+submit.getStudentID()+"' and type='3'");
			  if(res1.next())
			  {
				  String classID = res1.getString(1);
				  System.out.println("class:     "+classID);
				  System.out.println("SELECT teacherID FROM classincourse WHERE semID='"+sem+"' and classID='"+classID+"'and courseID='"+submit.getCourseID()+"'");
				  ResultSet res2=stmt.executeQuery("SELECT teacherID FROM classincourse WHERE semID='"+sem+"' and classID='"+classID+"'and courseID='"+submit.getCourseID()+"'");
				  if(res2.next())
				  {
					  ass.setTeacherID(res2.getString(1));
					  System.out.println("teacher:       "+ass.getTeacherID());
					  myFile = new MyFile(submit.getSubFile().getName()) ;
					  myFile.initArray(submit.getSubFile().getSize());
					  myFile.setSize(submit.getSubFile().getSize());
					  myFile.setMybytearray(submit.getSubFile().getMybytearray());
					    
					    
					  Save save = new Save(myFile);
					  save.saveFile(myFile);
					  File file = save.getFf(); 
					  
						  stmt.executeUpdate("UPDATE studentass SET status=1 WHERE semID='"+sem+"' and studentID='"+submit.getStudentID()+"'and courseID='"+submit.getCourseID()+"'and assNum='" +submit.getAssNum()+"'");
						  System.out.println("INSERT INTO sub VALUES        ('"  + ass.getCourseID() + "','" + submit.getStudentID() + "','" + submit.getAssNum() + "','0' , 'null','"+submit.getDate()+"','"+ submit.getSubFile().getName()+"','"+sem+"' ,'"+ass.getTeacherID()+"','"+submit.getSubFile().getSize()+"','null','0','0' )");		
						  stmt.executeUpdate("INSERT INTO sub VALUES        ('"  + ass.getCourseID() + "','" + submit.getStudentID() + "','" + submit.getAssNum() + "','0' , 'null','"+submit.getDate()+"','"+ submit.getSubFile().getName()+"','"+sem+"' ,'"+ass.getTeacherID()+"','"+submit.getSubFile().getSize()+"','null','0','0' )");
					      System.out.println("xxxxxxxxxxxxx: "+myFile.toString()); 
					      return "sucss";
				  }
			  }
		  }
	
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";
}

/**
 * this method return list that contain the parent's children
 * @param conn is connection
 * @param msg is string parentID
 * @return list that contain the parent's childrenID 
 * @throws SQLException
 */
public LinkedList<String> ParentChilds(Connection conn, Object object) throws SQLException {
	String id = (String)object;
	LinkedList<String> list = new LinkedList<String>();
	stmt = conn.createStatement();
	
	ResultSet res1 = stmt.executeQuery("SELECT * FROM studentparent WHERE parentID='"+id+"'");
	while (res1.next())
			list.add(res1.getString(2));
	return list;
}

/**
 * this method return the parent name
 * @param conn is connection
 * @param msg is string parentID
 * @return the parent name
 * @throws SQLException
 */
	public String ParentName(Connection conn, Object object) throws SQLException {	
	stmt = conn.createStatement();
	ResultSet res = stmt.executeQuery("SELECT * FROM parents WHERE ID='"+object.toString()+"'");
	res.next();
	return res.getString(2);
}
	
	/**
	 * this method return studentlist 
	 * @param conn is connection
	 * @return studentlist 
	 * @throws SQLException
	 */
	public LinkedList<StudentList> Studentlist(Connection conn) throws SQLException {
		stmt = conn.createStatement();
		LinkedList<StudentList> list = new LinkedList<StudentList>();
		ResultSet res=stmt.executeQuery("SELECT * FROM studentlist");
		while (res.next())
		{
			StudentList SL = new StudentList(res.getInt(1),res.getString(2),res.getString(3),res.getString(4));
			list.add(SL);
		}
		return list;
	}
	
	
	/**
	 * this method return course 
	 * @param conn is connection
	 * @param object
	 * @return course 
	 * @throws SQLException
	 */
	public Course getCourse(Connection conn, Object object) throws SQLException {
		stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery("SELECT * FROM course WHERE ID='"+object.toString()+"'");
		res.next();
		Course C = new Course(res.getString(1),res.getString(2),res.getString(3),res.getString(4));
		return C;
	}
	
	/**
	 * this method return preCourse list
	 * @param conn is connection
	 * @param msg is courseID
	 * @return preCourse list of this courseID 
	 * @throws SQLException
	 */
	public LinkedList<String> getPreCourse(Connection conn, Object object) throws SQLException {
		LinkedList<String> C = new LinkedList<String>();
		stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery("SELECT * FROM precourse WHERE courseID='"+object.toString()+"'");
		while (res.next()) C.add(res.getString(2));
		return C;
	}
	
	
	/**
	 * this method return list sub
	 * @param conn is connection
	 * @param msg is sub
	 * @return  list sub
	 * @throws SQLException
	 */
	public Object getSubList(Connection conn, Object msg)  throws SQLException  {
		stmt = conn.createStatement();
		MyFile myFile;
		Sub submitssion = (Sub)msg;
		ArrayList<Sub> list = new ArrayList<Sub>();
		System.out.println("SELECT * FROM sub WHERE status='0' and courseID='"+submitssion.getCourseID()+"' and assNum = '"+submitssion.getAssNum()+"' and semID='"+sem+"' and teacherID='"+submitssion.getTeacherID()+"'");
		ResultSet res=stmt.executeQuery("SELECT * FROM sub WHERE status='0' and courseID='"+submitssion.getCourseID()+"' and assNum = '"+submitssion.getAssNum()+"' and semID='"+sem+"' and teacherID='"+submitssion.getTeacherID()+"'");
		while (res.next())
		{
			File f = new File ("C://group14//"+res.getString(7));
			Browse b = new Browse(f);
			myFile = b.getFile();
			if (myFile.getName()!="null")
			{
				Sub sub = new Sub(res.getString(1),res.getString(2),res.getInt(3),res.getString(6),res.getString(8),res.getString(9),myFile);
				list.add(sub);
			}
		}
		if (!list.isEmpty())
			return list;
		else
			return"NoAss";
	}
	
	/**
	 * this method return the return date of the sub
	 * @param conn is connection
	 * @param msg is Sub
	 * @return string of the return date
	 * @throws SQLException
	 */
	public Object returnDate(Connection conn, Object msg) throws SQLException  {
		String date = null;
		stmt = conn.createStatement();
		Sub submitssion = (Sub)msg;
		System.out.println("SELECT * FROM assignment WHERE courseID='"+submitssion.getCourseID()+"' and assNum = '"+submitssion.getAssNum()+"' and semID='"+sem+"' and teacherID='"+submitssion.getTeacherID()+"'");
		ResultSet res=stmt.executeQuery("SELECT subDate FROM assignment WHERE courseID='"+submitssion.getCourseID()+"' and assNum = '"+submitssion.getAssNum()+"' and semID='"+sem+"' and teacherID='"+submitssion.getTeacherID()+"'");
		if (res.next())
		{
			date = res.getString(1);
			return date;
		}
		else 
		{
			return"SQLErr";
		}
	}
	
	/**
	 * this method insert the check assignment to the data base
	 * @param conn is connection
	 * @param msg is Sub
	 * @return true case of success or "SQLErr" case of err
	 */
	public Object ReturnCheckAss(Connection conn, Object msg) {
		MyFile myFile = null;
		Sub submit = (Sub)msg;
		System.out.println("SubmitAss");
		try 
		{    
			System.out.println("Try to save check ass");
			stmt = conn.createStatement();
			System.out.println("UPDATE sub SET status=1 , grade='"+submit.getGrade()+"',comment='"+submit.getComment()+"', reSize='"+submit.getCheckFile().getSize()+"',reSub='"+submit.getCheckFile().getName()+"' WHERE semID='"+sem+"' and studentID='"+submit.getStudentID()+"'and courseID='"+submit.getCourseID()+"'and assNum='" +submit.getAssNum()+"'");
			stmt.executeUpdate("UPDATE sub SET status=1 , grade='"+submit.getGrade()+"',comment='"+submit.getComment()+"', reSize='"+submit.getCheckFile().getSize()+"',reSub='"+submit.getCheckFile().getName()+"' WHERE semID='"+sem+"' and studentID='"+submit.getStudentID()+"'and courseID='"+submit.getCourseID()+"'and assNum='" +submit.getAssNum()+"'");
			Save save = new Save(submit.getCheckFile());
			save.saveFile(submit.getCheckFile());
			return true;
		 }
		 catch (SQLException e) {e.printStackTrace();}
		 return "SQL Err";
	}

	/**
	 * this method return the check assignment from the data base
	 * @param conn is connection
	 * @param msg is Sub
	 * @return sub case of success or "SQLErr" case of err
	 */
public Object OpenCheckAss(Connection conn, Object msg) {
	MyFile myFile = null;
	Assignment ass = new Assignment();
	Sub submit = (Sub)msg;
	try 
	{    
		System.out.println("Try to open checked assignment");
		  stmt = conn.createStatement();
		  System.out.println("SELECT * FROM sub WHERE semID='"+sem+"' and studentID='"+submit.getStudentID()+"' and assNum='"+submit.getAssNum()+"' and courseID='"+ submit.getCourseID()+"' and status='1'");
		  ResultSet res=stmt.executeQuery("SELECT * FROM sub WHERE semID='"+sem+"' and studentID='"+submit.getStudentID()+"' and assNum='"+submit.getAssNum()+"' and courseID='"+ submit.getCourseID()+"' and status='1'");
		  if(res.next())
		  {
			  File f = new File ("C://group14//"+res.getString(11));
			  Browse b = new Browse(f);
			  myFile = b.getFile();
			  submit.setCheckFile(myFile);
			  submit.setGrade(res.getInt(4));
			  submit.setComment(res.getString(5));
			  
			  return submit;
		  }
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "SQL Err";
}

/**
 * this method return the check assignment's from the data base to the student
 * @param conn is connection
 * @param msg is studentID
 * @return assignment list case of success or "SQLErr" case of err
 */
public Object ReceiveCheckedAssn(Connection conn, Object msg) {
	 System.out.println("Try to open student assignments");
	 Assignment ass = new Assignment();
	 ArrayList<Assignment> l = new ArrayList<Assignment>();
	 String id = (String)msg;
	 try 
	 {    
		  stmt = conn.createStatement();
		  System.out.println("SELECT * FROM sub WHERE semID = '"+sem+"' and studentID='"+id+"' and status='1'");
		  ResultSet res=stmt.executeQuery("SELECT * FROM sub WHERE semID = '"+sem+"' and studentID='"+id+"' and status='1'");
		  if (!res.next()) // user has assignment 
		  {
			  return "NoAssignment";
		  }
		  else
		  {
			  ass.setCourseID(res.getString(1));
			  ass.setAssNumber(res.getInt(3));
			  l.add(ass);
			  while (res.next()) 
			  {
				  ass = new Assignment();
				  ass.setCourseID(res.getString(1));
				  ass.setAssNumber(res.getInt(3));
				  l.add(ass);
			  }
			  return l;	  
		  }
	 }
	 catch (SQLException e) {e.printStackTrace();}
	 return "NoAssignment";
	}	
	
/**
 * this method add grade to the data base
 * @param conn is connection
 * @param msg is Sub
 * @return sub case of success or "SQLErr" case of err
 */
	public Object AddGrade(Connection conn, Object msg) {
		Grades grade = (Grades)msg;
		try 
		{    
			System.out.println("Try to add student grade");
			stmt = conn.createStatement();
			System.out.println("INSERT INTO grades VALUES   ('"  + grade.getStudentID() + "','" + grade.getCourseID() + "','" + sem + "','"+grade.getGrade()+"' ,'"+grade.getEform()+"','"+grade.getClassID()+"','"+grade.getTeacherID()+"' )");	
			stmt.executeUpdate("INSERT INTO grades VALUES   ('"  + grade.getStudentID() + "','" + grade.getCourseID() + "','" + sem + "','"+grade.getGrade()+"' ,'"+grade.getEform()+"','"+grade.getClassID()+"','"+grade.getTeacherID()+"' )");	
			return true;
		}
		 catch (SQLException e) {e.printStackTrace();}
		 return "SQL Err";
	}
	
	/**
	 * this method return the student of the teacher
	 * @param conn is connection
	 * @param msg is array of string
	 * @return studentlist of the teacher's student case of success or "SQLErr" case of err
	 */
	public Object returnTeachersStudents(Connection conn, Object msg) {
		String arr[] = (String[])msg;
		ArrayList<String> studentList   = new ArrayList<String>();
		ArrayList<String> classList   = new ArrayList<String>();
		try 
		{				
			stmt = conn.createStatement();
				System.out.println("SELECT ID FROM studentlist WHERE type='3' and k='"+arr[2]+"' ");	
					ResultSet res2 = stmt.executeQuery("SELECT ID FROM studentlist WHERE type='3' and k='"+arr[2]+"' ");	
					if (res2.next())
					{
						do  
						{
							studentList.add(res2.getString(1));
						}while(res2.next());
					}
			return studentList;
		}
		catch (SQLException e) {e.printStackTrace();}
		return "SQL Err";
	}

	/**
	 * this method return the Dynamic Grades
	 * @param conn is connection
	 * @param msg is array of string
	 * @return Dynamic Grades
	 */
	public LinkedList<GQuery> getDynamicGrades(Connection conn, Object msg) throws SQLException {
		
		stmt = conn.createStatement();
		Object id[]=(Object[]) msg; // id[0] = per what; id[1] = teacherid/courseid/classid; id[2] = id for id[1]
		LinkedList<GQuery> list = new LinkedList<GQuery>();
		
		String ForWhat = id[0].toString();
		String PerWhat = id[1].toString();
		String ID = id[2].toString();
		
		System.out.println("I HAVE THOSE: "+PerWhat+" "+ForWhat+" "+ID);
		GQuery GQ;
		ResultSet res = stmt.executeQuery("SELECT "+ PerWhat+ ", count(grade) AS Count, max(grade) AS Max, min(grade) as Min, avg(grade) as AVG  FROM grades WHERE "+ForWhat+ "='"+ID+"' group by "+PerWhat);
		while (res.next())
		{
			//	GQuery(String iD, int grade, int count, int max, int min, int avg)
			GQ =  new GQuery(res.getString(1),res.getInt(2),res.getInt(3),res.getInt(4),res.getInt(5));
			System.out.println("ID: "+GQ.getID()+" Count: "+GQ.getCount()+" AVG: "+GQ.getAvg()+" Min: "+GQ.getMin()+" Max: "+GQ.getMax());
			list.add(GQ);
		}
			
		return list;
	}
}